#ifndef SQL_H
#define SQL_H
#include <QObject>

const QString SQL_CREATETABLE_VOCABULARY = "CREATE TABLE IF NOT EXISTS vocabulary(" \
    "id INTEGER PRIMARY KEY AUTOINCREMENT, " \
    "vocabulary_name VARCHAR(30), " \
    "size UNSIGNED INTEGER" \
    ") ";
const QString SQL_CREATETABLE_WORD = "CREATE TABLE IF NOT EXISTS word(" \
    "id INTEGER PRIMARY KEY AUTOINCREMENT, " \
    "original VARCHAR(50), " \
    "translation VARCHAR(50), " \
    "vocabulary_id INTEGER" \
    ")";
const QString SQL_CREATETABLE_USER = "CREATE TABLE IF NOT EXISTS user(" \
    "id INTEGER PRIMARY KEY AUTOINCREMENT, " \
    "current_vocabulary_id INTEGER, " \
    "group_size INTEGER, " \
    "current_group INTEGER " \
    ")";

const QString SQL_INSERT_VOCABULARY = "INSERT INTO vocabulary(vocabulary_name, size) VALUES(?, ?)";
const QString SQL_INSERT_WROD = "INSERT INTO word(original, translation, vocabulary_id) VALUES(?, ?, ?)";
const QString SQL_INSERT_USER = "INSERT INTO user(current_vocabulary_id, group_size, current_group) VALUES(?, ?, ?)";

const QString SQL_SELECT_VOCABULARY_ALL = "SELECT * FROM vocabulary";
const QString SQL_SELECT_WORD_ALL = "SELECT * FROM word";
const QString SQL_SELECT_WORD_BY_VOCABULARY = "SELECT * FROM word WHERE vocabulary_id=? ";

const QString SQL_SELECT_USER_ALL = "SELECT user.id, user.current_vocabulary_id, user.group_size, " \
                                        "vocabulary.vocabulary_name, user.current_group " \
                                    "FROM user LEFT JOIN vocabulary " \
                                        "ON user.current_vocabulary_id=vocabulary.id ";

const QString SQL_SELECT_VOCABULAEY = "SELECT * FROM vocabulary WHERE id=?";

const QString SQL_UPDATE_USER = "UPDATE user SET current_vocabulary_id=?, group_size=? " \
                                "WHERE id = ? ";
const QString SQL_UPDATE_USER_CURRENT_GROUP = "UPDATE user SET current_group=? " \
                                "WHERE id = ? ";

#endif // SQL_H
